﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace UniRlv.Utility
{
    public class SqlHelper
    {
        //链接字符串
        public static string connStr = "";

        /// <summary>
        /// 做增删改的功能
        /// </summary>
        /// <param name="sql">SQl语句</param>
        /// <param name="ps">Sql语句中的参数</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteNonQuery(string sql, ref string errMsg, params SqlParameter[] ps)
        {
            //链接数据库
            using (SqlConnection con = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    try
                    {
                        con.Open();//打开数据库
                        if (ps != null)
                        {
                            cmd.Parameters.AddRange(ps);
                        }

                        return cmd.ExecuteNonQuery();
                    }
                    catch (System.Exception ex)
                    {
                        con.Close();
                        con.Dispose();
                        errMsg = ex.Message;
                        return -1;
                    }
                }
            }
        }

        /// <summary>
        /// 首行首列
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">sql语句的参数</param>
        /// <returns>返回首行首列</returns>
        public static object ExecuteScalar(string sql, ref string errMsg, params SqlParameter[] ps)
        {
            using (SqlConnection con = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    try
                    {
                        con.Open();//打开数据库
                        if (ps != null)
                        {
                            cmd.Parameters.AddRange(ps);
                        }

                        return cmd.ExecuteScalar();
                    }
                    catch (System.Exception ex)
                    {
                        con.Close();
                        con.Dispose();
                        errMsg = ex.Message;
                        return null;
                    }
                }
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">sql语句中的参数</param>
        /// <returns>返回Reader</returns>
        public static SqlDataReader ExecuteReader(string sql, ref string errMsg, params SqlParameter[] ps)
        {
            SqlConnection con = new SqlConnection(connStr);
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                if (ps != null)
                {
                    cmd.Parameters.AddRange(ps);
                }
                try
                {
                    con.Open();//打开数据库
                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
                catch (Exception ex)
                {
                    con.Close();
                    con.Dispose();
                    errMsg = ex.Message;
                    return null;
                }

            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">sql语句中的参数</param>
        /// <returns>返回表</returns>
        public static DataTable ExecuteTable(string sql, ref string errMsg, params SqlParameter[] ps)
        {
            DataTable dt = new DataTable();

            using (SqlDataAdapter sda = new SqlDataAdapter(sql, connStr))
            {
                try
                {
                    if (ps != null)
                    {
                        sda.SelectCommand.Parameters.AddRange(ps);
                    }

                    sda.Fill(dt);

                    return dt;
                }
                catch (System.Exception ex)
                {
                    sda.Dispose();
                    errMsg = ex.Message;
                    return null;
                }
            }
        }

        /// <summary>
        /// 通过存储过程查询数据
        /// </summary>
        /// <param name="sql">存储过程名</param>
        /// <param name="ps">存储过程的参数</param>
        /// <returns>返回reader</returns>
        public static SqlDataReader ExecuteQueryProc(string procName, ref string errMsg, params SqlParameter[] ps)
        {
            SqlConnection con = new SqlConnection(connStr);
            using (SqlCommand cmd = new SqlCommand(procName, con))
            {
                if (ps != null)
                {
                    cmd.Parameters.AddRange(ps);
                }
                try
                {
                    con.Open();//打开数据库
                    cmd.CommandType = CommandType.StoredProcedure;
                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
                catch (Exception ex)
                {
                    con.Close();
                    con.Dispose();
                    errMsg = ex.Message;
                    return null;
                }

            }
        }

        /// <summary>
        /// 通过存储过程做增删改的功能
        /// </summary>
        /// <param name="sql">存储过程名</param>
        /// <param name="ps">存储过程的参数</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteNonQueryProc(string procName, ref string errMsg, params SqlParameter[] ps)
        {
            //链接数据库
            using (SqlConnection con = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(procName, con))
                {
                    try
                    {
                        con.Open();//打开数据库
                        if (ps != null)
                        {
                            foreach (SqlParameter p in ps)
                            {
                                if (p.Value == null)
                                {
                                    p.Value = DBNull.Value;
                                }
                            }

                            cmd.Parameters.AddRange(ps);
                        }

                        cmd.CommandType = CommandType.StoredProcedure;
                        return cmd.ExecuteNonQuery();
                    }
                    catch (System.Exception ex)
                    {
                        con.Close();
                        con.Dispose();
                        errMsg = ex.Message;
                        return -1;
                    }
                }
            }
        }

        /// <summary>
        /// 根据存储过程查询首行首列
        /// </summary>
        /// <param name="sql">存储过程名</param>
        /// <param name="ps">sql语句的参数</param>
        /// <returns>返回首行首列</returns>
        public static object ExecuteScalarProc(string procName, ref string errMsg, params SqlParameter[] ps)
        {
            using (SqlConnection con = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(procName, con))
                {
                    try
                    {
                        con.Open();//打开数据库
                        if (ps != null)
                        {
                            cmd.Parameters.AddRange(ps);
                        }
                        cmd.CommandType = CommandType.StoredProcedure;

                        return cmd.ExecuteScalar();
                    }
                    catch (System.Exception ex)
                    {
                        con.Close();
                        con.Dispose();
                        errMsg = ex.Message;
                        return -1;
                    }
                }
            }
        }

        public static bool ExecuteSqlTran(string[] SQLStringList, ref string msg)
        {
            using (SqlConnection con = new SqlConnection(connStr))
            {
                SqlCommand oleDbCommand = new SqlCommand();
                SqlTransaction oleDbTransaction;

                con.Open();
                oleDbCommand.Connection = con;
                oleDbTransaction = con.BeginTransaction();
                oleDbCommand.Transaction = oleDbTransaction;

                try
                {

                    for (int i = 0; i < SQLStringList.Length; i++)
                    {
                        string text = SQLStringList[i];
                        if (text.Trim().Length > 1)
                        {
                            oleDbCommand.CommandText = text;
                            oleDbCommand.ExecuteNonQuery();
                        }
                    }
                    oleDbTransaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    oleDbTransaction.Rollback();
                    msg = e.Message;
                    con.Close();

                    return false;
                }
            }
        }

        /// <summary>
        /// 查询表格所有列
        /// </summary>
        /// <param name="tbName">表格名</param>
        /// <returns>列名列表</returns>
        public static List<string> GetAllColumns(string tbName, ref string errMsg)
        {
            string sql = @"select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name=@tbName)";
            DataTable dt = ExecuteTable(sql, ref errMsg, new SqlParameter("@tbName", tbName));

            List<string> list = new List<string>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                list.Add(dt.Rows[i]["name"].ToString());
            }

            return list;
        }

        public static bool IfTableExist(string tbName)
        {
            string sql = string.Format("select COUNT(*) from dbo.sysobjects where id = object_id(N'{0}') and OBJECTPROPERTY(id, N'IsUserTable') = 1", tbName);
            string msg = string.Empty;

            object obj = SqlHelper.ExecuteScalar(sql, ref msg);

            if (string.IsNullOrEmpty(msg))
            {
                return obj != null && Convert.ToInt32(obj) > 0;
            }

            return false;
        }

        public static bool IfColumnExist(string tbName, string col)
        {
            string sql = string.Format("select COL_LENGTH( '{0}','{1}')", tbName, col);
            string msg = string.Empty;

            object obj = SqlHelper.ExecuteScalar(sql, ref msg);

            if (string.IsNullOrEmpty(msg))
            {
                return obj != null && obj != DBNull.Value && Convert.ToInt32(obj) > 0;
            }

            return false;
        }


        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="tbDest">目标表格</param>
        /// <param name="dtSrc">源DataTable</param>
        /// <param name="sm">列映射</param>
        public static void ExcuteBulkCopy(string tbDest, DataTable dtSrc, params SqlBulkCopyColumnMapping[] sm)
        {
            using (SqlBulkCopy sqlBC = new System.Data.SqlClient.SqlBulkCopy(connStr, SqlBulkCopyOptions.UseInternalTransaction))
            {
                //sqlBC.BatchSize = 1000;
                sqlBC.DestinationTableName = tbDest;

                if (sm != null)
                {
                    foreach (SqlBulkCopyColumnMapping obj in sm)
                    {
                        sqlBC.ColumnMappings.Add(obj);
                    }
                }

                sqlBC.WriteToServer(dtSrc);
            }
        }

        /// <summary>
        /// 批量更新
        /// </summary>
        /// <param name="dtSrc">源DataTable</param>
        /// <param name="sql">sql语句</param>
        /// <param name="ps">命令参数</param>
        /// <returns>受影响行数</returns>
        public static int ExcuteBulkUpdate(DataTable dtSrc, string sql, params SqlParameter[] ps)
        {
            //链接数据库
            using (SqlConnection con = new SqlConnection(connStr))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    try
                    {
                        con.Open();//打开数据库

                        sda.UpdateCommand = new SqlCommand(sql, con);

                        if (ps != null)
                        {
                            sda.UpdateCommand.Parameters.AddRange(ps);
                        }

                        sda.UpdateBatchSize = 0;
                        sda.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

                        return sda.Update(dtSrc);
                    }
                    catch (System.Exception ex)
                    {
                        con.Close();
                        con.Dispose();
                        return -1;
                    }
                }
            }
        }
    }
}
